package com.dudu.dataImport;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class PoolToPool_Account_RelforAppUtil {
	/**
	 * 根据app账号导入
	 * 从sourceConn中t_pool_account_rel、t_rule_account、t_account中根据条件导入到
	 * targetConn中的t_pool_account_rel、t_rule_account
	 * @param sourceConn 源数据库连接
	 * @param targetConn 目的数据库连接
	 * @param appid  应用ID,多个使用逗号分隔
	 */
	public void dataConversion(Connection sourceConn,Connection targetConn,String appid){
		Statement stmt = null;
		Statement stmt_rel = null;
		Statement stmt_rule=null;
		MyUtil myUtil = new MyUtil();
		try{
			stmt=sourceConn.createStatement();
			/*sql 拼接*/
			StringBuffer sql=new StringBuffer();
			sql.append("select id,identifier,product_code from t_account  where category='01' ");//正式账号
			sql.append(" and account_type='app'");// 应用账号
			if(null!=appid &&  appid.length()>0){ //只导部分应用
				sql.append(" and app_id in ("+appid+")");
			}
			sql.append(" order by id");
			/*执行查询*/
			ResultSet accountresult = stmt.executeQuery(sql.toString());
			/*打印总条数*/
			accountresult.last();//移到最后一行   
			int rowCount = accountresult.getRow(); //得到当前行号，也就是记录数   
			accountresult.beforeFirst(); //如果还要用结果集，就把指针再移到初始化的位置
			System.out.println("accountresultsql="+sql.toString()+",resultCount="+rowCount);
			if(rowCount>0){
				/*遍历accountresult*/
				while (accountresult.next()) {
					/*获取数据*/
					String account_id=myUtil.isNull(accountresult.getString("id"));//ID
					String cust_account="-1";//计费账号
					String app_key=myUtil.isNull(accountresult.getString("identifier"));//账号标识
					String product_code=myUtil.isNull(accountresult.getString("product_code"));//外系统编码
					/*创建Statement*/
					Statement stmt0=targetConn.createStatement();
					stmt_rel=sourceConn.createStatement();
					/*t_pool_account_rel*/
					/*拼接sql*/
					String relsql="select * from t_pool_account_rel where account_id="+account_id;
					/*执行查询*/
					ResultSet relresult=stmt_rel.executeQuery(relsql);
					/*打印总条数*/
					relresult.last();//移到最后一行   
					int rowCount1 = relresult.getRow(); //得到当前行号，也就是记录数   
					relresult.beforeFirst(); //如果还要用结果集，就把指针再移到初始化的位置
					System.out.println("relresultsql="+relsql.toString()+",resultCount="+rowCount1);
					/*遍历relresult*/
					while(relresult.next()){
						/*获取数据*/
						String id=myUtil.isNull(relresult.getString("id"));
						int pool_num_id=relresult.getInt("pool_num_id");//号码能力t_pool_ability_info的id
						String number_vest=myUtil.isNull(relresult.getString("number_vest"));//归属地区
						String email_notice=myUtil.isNull(relresult.getString("email_notice"));//邮件通知
						String switch_num=myUtil.isNull(relresult.getString("switch_num"));//主动切换号码
						String create_time=myUtil.isNull(relresult.getString("create_time"));//数据创建时间
						String update_time=myUtil.isNull(relresult.getString("update_time"));//数据修改时间
						try {
							/*判断导入时新表是否存在*/
							Boolean flag=this.isNewRow(stmt0, "t_pool_account_rel", pool_num_id+"", app_key);
							if(flag){
								/*拼接sql*/
								StringBuffer relnewsql=new StringBuffer();
								relnewsql.append("insert into t_pool_account_rel(pool_num_id,app_key,cust_account,periphery_code");
								relnewsql.append(",number_vest,email_notice,switch_num,create_time,update_time) values ("+pool_num_id);
								relnewsql.append(",'").append(app_key).append("','").append(cust_account).append("','");
								relnewsql.append(product_code).append("','");
								relnewsql.append(number_vest).append("','").append(email_notice).append("','");
								relnewsql.append(switch_num).append("','").append(create_time).append("','").append(update_time).append("')");
								System.out.println("instertt_pool_account_relnewsql="+relnewsql.toString());
								/*执行intsert命令*/
								stmt0.executeUpdate(relnewsql.toString(),Statement.RETURN_GENERATED_KEYS);
								/*获取intsert的ID*/
								ResultSet keys = stmt0.getGeneratedKeys(); 
								if(keys.next()) {
									System.out.println("正在导入id="+id+",account_id="+account_id+"时，导入t_pool_account_rel成功 ！！新表t_pool_account_rel对应ID="+keys.getInt(1));
								}
								targetConn.commit();
							}else{
								System.out.println("正在导入id="+id+",account_id="+account_id+"时，导入t_pool_account_rel失败 ！！新表t_pool_account_rel中有此条数据");
							}
						} catch (Exception e) {
							targetConn.rollback();
							targetConn.close();
							sourceConn.close();
					        stmt.close();
					        stmt_rel.close();
					        stmt_rule.close();
					        System.out.println("正在导入id="+id+",account_id="+account_id+"时，导入t_pool_account_rel失败，失败原因："+e.getMessage());
							e.printStackTrace();
						}
					}
					System.out.println("*****************************");
					System.out.println("*****************************");
					/*t_rule_account*/
					/*创建Statement*/
					stmt_rule=sourceConn.createStatement();
					/*拼接sql*/
					String rulesql="select * from t_rule_account where account_id="+account_id;
					/*执行命令*/
					ResultSet ruleresult=stmt_rule.executeQuery(rulesql);
					/*打印总条数*/
					ruleresult.last();//移到最后一行   
					int rowCount2 = ruleresult.getRow(); //得到当前行号，也就是记录数   
					ruleresult.beforeFirst(); //如果还要用结果集，就把指针再移到初始化的位置
					System.out.println("t_rule_accountsql="+rulesql.toString()+",resultCount="+rowCount2);
					/*遍历ruleresult*/
					while(ruleresult.next()){
						String id=myUtil.isNull(ruleresult.getString("id"));
						int rule_id=ruleresult.getInt("rule_id");//能力ID
						String account_type=myUtil.isNull(ruleresult.getString("account_type"));//账号类型
						String rule_names=myUtil.isNull(ruleresult.getString("rule_names"));//能力名称
						String create_time=myUtil.isNull(ruleresult.getString("create_time"));//规则设置时间
						try {
							/*判断导入时新表是否存在*/
							Boolean flag=this.isNewRow(stmt0, "t_rule_account", rule_id+"", app_key);
							if(flag){
								/**
								 * 拼接SQL
								 */
								StringBuffer rulenewsql=new StringBuffer();
								rulenewsql.append("insert into t_rule_account(rule_id,app_key,cust_account,periphery_code");
								rulenewsql.append(",account_type,rule_names,create_time) values ("+rule_id);
								rulenewsql.append(",'").append(app_key).append("','").append(cust_account).append("','");
								rulenewsql.append(product_code).append("','");
								rulenewsql.append(account_type).append("','").append(rule_names).append("','");
								rulenewsql.append(create_time).append("')");
								System.out.println("instert_rule_accountnewsql="+rulenewsql.toString());
								/*执行intsert命令*/
								stmt0.executeUpdate(rulenewsql.toString(),Statement.RETURN_GENERATED_KEYS);
								/*获取intsert的ID*/
								ResultSet keys = stmt0.getGeneratedKeys(); 
								if(keys.next()) {
									System.out.println("正在导入id="+id+",account_id="+account_id+"时，导入t_rule_account成功 ！！新表t_rule_account对应ID="+keys.getInt(1));
								}
								targetConn.commit();
							}else{
								System.out.println("正在导入id="+id+",account_id="+account_id+"时，导入t_rule_account失败 ！！新表t_rule_account中有此条数据");
							}
							
						} catch (Exception e) {
							targetConn.rollback();
							targetConn.close();
							sourceConn.close();
					        stmt.close();
					        stmt_rel.close();
					        stmt_rule.close();
					        System.out.println("正在导入id="+id+",account_id="+account_id+"时，导入t_rule_account失败，失败原因："+e.getMessage());
							e.printStackTrace();
						}
					}
				}					
			}
			stmt.close();
		    stmt_rel.close();
		    stmt_rule.close();
		    targetConn.commit();
		    targetConn.close();
		    sourceConn.close();
		}catch(Exception e){
			try {
				targetConn.rollback();
				targetConn.close();
				sourceConn.close();
		        stmt.close();
		        stmt_rel.close();
		        stmt_rule.close();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
	}
	/**
	 * 判断导入时新表是否存在
	 * @param targetConn
	 * @param stmt0
	 * @param table
	 * @param keyvalue
	 * @return
	 * @throws Exception
	 */
	public static boolean isNewRow(Statement stmt0,String table,String keyvalue,String cust_account)throws Exception{
		String sql="select * from "+table;
		if(table.equals("t_pool_account_rel")){
			sql=sql+" where pool_num_id="+keyvalue+" and app_key='"+cust_account+"'";
		}else if(table.equals("t_rule_account")){
			sql=sql+" where rule_id="+keyvalue+" and app_key='"+cust_account+"'";
		}
		/*执行查询*/
		ResultSet accountresult=stmt0.executeQuery(sql);
		/*打印总条数*/
		accountresult.last();//移到最后一行   
		int rowCount = accountresult.getRow(); //得到当前行号，也就是记录数   
		//System.out.println("**********"+sql+"------"+rowCount);
		if(rowCount>0){
			return false;
		}else{
			return true;
		}
	}
	public static void main(String[] args) {
		/*源*/
		String sourceUrl="jdbc:mysql://123.56.31.231:9596/voip_plat_version3?useUnicode=true&amp;characterEncoding=UTF-8";
		String sourceUsername="root";
		String sourcePassword="lofjel(lkKe839*J2@3";
		/*目的*/
		//String targetUrl="jdbc:mysql://172.20.14.224:3306/voip_dudu?useUnicode=true&characterEncoding=utf-8";
		//String targetUsername="voip_plat";
		//String targetPassword="dudu_voip_2016";
		String targetUrl="jdbc:mysql://123.56.31.231:9596/voip_plat_version4?useUnicode=true&amp;characterEncoding=UTF-8";
		String targetUsername="root";
		String targetPassword="lofjel(lkKe839*J2@3";
		String appid="43";//"87,72";//应用ID 多个时使用逗号分隔87,72
		/*创建连接*/
		DBConnectionUtil dbUtil = new DBConnectionUtil();
		Connection sourceConn = dbUtil.getDBConnection(sourceUrl,sourceUsername,sourcePassword);
		Connection targetConn = dbUtil.getDBConnection(targetUrl, targetUsername, targetPassword);
		/*执行*/
		PoolToPool_Account_RelforAppUtil util=new PoolToPool_Account_RelforAppUtil();
		util.dataConversion(sourceConn, targetConn, appid);
		System.out.println("执行完成执行以下sql");
		//delete from t_pool_ability_info where id not in (   select pool_num_id from t_pool_account_rel  );
		//commit;
		//delete from t_pool_number where id not in(select pool_id from  t_pool_ability_info );
		//commit;
	}

}
